Sample Scripts

PERPETUUM (VB reject function)

Although this is a very easy function, it needs some explanation what it does. 

Suppose you work out a database synchronisation project where you have one master database people in the office are using, and a series of slave databases people on the road are using. People on the road are usually offline and connect themselves ones in a while to the master database to send the updates they made, and to get all updates other people made.

When you follow the trigger mechanism you understand that the TRB table only contains changes of the database you're working on. Assuming a machine is a long period offline and connects to the master machine you are always able to exchange only the changes to both database:

• Connection client runs on the master system: the changes are in the TRB of the slave between last pointer and current pointer.
• Connection client runs on the slave system: all changes are in the output buffer of the PRODBX connection client.

On the master machine you need only one connection client that handles the changes of the master database. However, on every change the master database will trigger only one line in TRB. It is up to the PRODBX connection client (and your configuration) to generate a transaction for each of the slaves.

Picture yourself the next scenario. Slave 1 has a change and generates a trigger. That trigger generates an update on the master database, and since it is a change, it generates a trigger on the master. PRODBX picks this up and sends a transaction to slave 1 and slave 2. So, slave 1 gets an update, and since it's an update, it generates a trigger, that is send to the master...   got the picture. We call it the perpetuum.

The solution is two-fold:

Slave generates only triggers of his own changes

This means that any update on the slave database because of a change in another slave or the master, may not generate a trigger. This can only be solved in the slave trigger. 

First you need to create an extra database table in the slave database that holds only the database id. So slave 1 would get id 1, Slave 2 would get 2... In our sample this table is called DBID and has one field 'lngdbid'

Secondly you must add to each table an extra field holding the id of the database that generated the last update of the record. Your application must support this by writing the DBID to this extra field, or work through strored procedures to get the correct DBID in this field. In our sample this field is also called lngdbid.

Adapt the trigger to generate only triggers when the DBID.lngdbid = TABLE.lngdbid. Below an example for SQL-server and insert trigger.

CREATE TRIGGER <Trigger name> on <Table name> for INSERT AS
DECLARE
    @realhead NUMERIC,
    @in_table NUMERIC,
    @maximum NUMERIC,
    @dbid NUMERIC,
    @origin NUMERIC,
    @sk1 VARCHAR(50),
    @sk2 VARCHAR(50),
    @sk3 VARCHAR(50),
    @sk4 VARCHAR(50),
    @sk5 VARCHAR(50),
    @nk1 NUMERIC,
    @nk2 NUMERIC,
    @nk3 NUMERIC,
    @nk4 NUMERIC,
    @nk5 NUMERIC
BEGIN
    set CURSOR_CLOSE_ON_COMMIT on
    SELECT @origin = lngdbid from DBID
    declare <Cursor name> cursor for select lngdbid,<Alpha field1>,<Alpha field2>,<Numeric field1> from inserted
    open <Cursor name>
    fetch next from <Cursor name> into @dbid,@sk1,@sk2,@nk1
    WHILE (@@FETCH_STATUS <> -1)
    begin
        IF @dbid = @origin 
        begin
            SELECT @realhead = HEADER,@maximum = MAXI FROM HEAD
            SELECT @in_table = MAX(IND) FROM TRB
            set @realhead=@realhead+1
            IF @realhead>@maximum 
            BEGIN
                set @realhead=1
            END
            IF @in_table<@realhead 
            BEGIN
                INSERT INTO TRB (IND,TABL,EVENT,SK1,SK2,SK3,SK4,SK5,NK1,NK2,NK3,NK4,NK5) values
                (@realhead,'<Table name>','1',@sk1,@sk2,NULL,NULL,NULL,@nk1,NULL,NULL,NULL,NULL)
                UPDATE HEAD SET HEADER=@realhead
            END 
            ELSE
            BEGIN
                UPDATE TRB SET IND=@realhead,TABL='<Table name>',EVENT='1',SK1=@sk1,
                SK2=@sk2,SK3=NULL,SK4=NULL,SK5=NULL,NK1=@nk1,NK2=NULL,NK3=NULL,NK4=NULL,NK5=NULL WHERE IND=@realhead;
                UPDATE HEAD SET HEADER=@realhead;
            END
        end
        fetch next from <Cursor name> into @dbid,@sk1,@sk2,@nk1
    end
    close <Cursor name>
    deallocate <Cursor name>
    commit 
END
GO

Master connection client may not send back a transaction

Since we added the additional field to each table holding the database id of the database generating the change, we can make it part of the source fields to retrieve when the we interpret the trigger coming from the master database. In our example the lngdbid would contain 1 if it comes from slave 1, and we must reject the output record that would go to slave 1. 

As many slaves we have we will call as many times lngdbid, each time for a different record id (which is the record id for one of the slaves). We pass lngdbid through the function PERPETUUM and write it to lngdbid of the destination record. But since we enabled the 'reject function' option, it will reject the record that goes to the same database initiating the change. (In our example slave 1).

Function PERPETUUM(InputValue,Parameter1,Parameter2,Parameter3,Parameter4)
     If Clng(InputValue) = Clng(Parameter3) Then
          PERPETUUM = True
     Else
          PERPETUUM = InputValue
     End If
End Function

In our configuration we write the database id in the Parameter 3 field. For slave 1, parameter 3 would be 1. In the function it is compared with lngdbid and if lngdbid=1 then the record is rejected and slave 1 would not get an update back of his own change.

Remark: there are possible variants to this mechanism by using the 'TRB field to STACK' option in the connection setup.